package com.thoughtworks;

import java.util.ArrayList;
import java.util.List;
import java.sql.*;

public class StudentRepository {

  public StudentRepository() {
    String buildStudentSql = "CREATE TABLE student(id VARCHAR(20) NOT NULL, name VARCHAR(30) NOT NULL, gender VARCHAR(10) NOT NULL," +
                             "admissionYear INT NOT NULL, birthday DATE NOT NULL, classId VARCHAR(20) NOT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8";
    try{
      Connection conn = DatabaseUtil.getConnection();
      Statement stmt = conn.createStatement();
      if(!validateTableExist("student")){
        System.out.println("student not exist, now create a new one.");
        stmt.execute(buildStudentSql);
      } else {
        System.out.println("student already exist!");
      }
    } catch (SQLException e){
      e.printStackTrace();
    }

  }

  public void save(List<Student> students) {
    students.forEach(this::save);
  }

  public void save(Student student) {
    // TODO:
    try{
      Connection conn = DatabaseUtil.getConnection();
      String sql = "INSERT INTO student(id, name, gender, admissionYear, birthday, classId)"
              +"values("+"?,?,?,?,?,?)";
      //预编译
      PreparedStatement ptmt = conn.prepareStatement(sql); //预编译SQL，减少sql执行

      //传参
      ptmt.setString(1, student.getId());
      ptmt.setString(2, student.getName());
      ptmt.setString(3, student.getGender());
      ptmt.setInt(4, student.getAdmissionYear());
      java.sql.Date sqlDate = new java.sql.Date(student.getBirthday().getTime());
      ptmt.setDate(5, sqlDate);
      ptmt.setString(6, student.getClassId());
      ptmt.execute();
    } catch (Exception e){
      e.printStackTrace();
    }

  }

  public List<Student> query() {
    // TODO:
    try{
      Connection conn = DatabaseUtil.getConnection();
      String sql = "select * from  student";
      //预编译SQL，减少sql执行
      Statement stmt = conn.createStatement();
      //执行
      ResultSet rs = stmt.executeQuery(sql);
      List<Student> students = new ArrayList<Student>();
      while(rs.next()) {
        Student student = new Student();
        student.setId(rs.getString("id"));
        student.setName(rs.getString("name"));
        student.setGender(rs.getString("gender"));
        student.setAdmissionYear(rs.getInt("admissionYear"));
        student.setBirthday(rs.getDate("birthday"));
        student.setClassId(rs.getString("classId"));
        students.add(student);
      }
      conn.close();
      stmt.close();
      return students;
    } catch (Exception e){
      e.printStackTrace();
      return null;
    }
  }

  public List<Student> queryByClassId(String classId) {
    // TODO:
    try{
      Connection conn = DatabaseUtil.getConnection();
      String sql = "SELECT * FROM  student WHERE classId=? ORDER BY id DESC";
      //预编译SQL，减少sql执行
      PreparedStatement ptmt = conn.prepareStatement(sql);
      ptmt.setString(1, classId);
      //执行
      ResultSet rs = ptmt.executeQuery();
      List<Student> students = new ArrayList<Student>();
      while(rs.next()) {
        Student student = new Student();
        student.setId(rs.getString("id"));
        student.setName(rs.getString("name"));
        student.setGender(rs.getString("gender"));
        student.setAdmissionYear(rs.getInt("admissionYear"));
        student.setBirthday(rs.getDate("birthday"));
        student.setClassId(rs.getString("classId"));
        students.add(student);
      }
      return students;
    } catch (Exception e){
      e.printStackTrace();
      return null;
    }
  }

  public void update(String id, Student student) {
    // TODO:
    try{
      Connection conn = DatabaseUtil.getConnection();
      String sql = "UPDATE student set name=? where id=?";
      PreparedStatement ptmt = conn.prepareStatement(sql);
      ptmt.setString(1, student.getName());
      ptmt.setString(2, id);
      ptmt.execute();
    }catch (Exception e){
      e.printStackTrace();
    }
  }

  public void delete(String id) {
    // TODO:
    try{
      Connection conn = DatabaseUtil.getConnection();
      String sql = "DELETE FROM student WHERE id=?";
      PreparedStatement ptmt = conn.prepareStatement(sql);
      ptmt.setString(1, id);
      ptmt.execute();
    }catch (Exception e){
      e.printStackTrace();
    }
  }

  public boolean validateTableExist(String tableName){
    boolean flag = false;
    try {
      Connection conn = DatabaseUtil.getConnection();
      DatabaseMetaData meta = conn.getMetaData();
      String type [] = {"TABLE"};
      ResultSet rs = meta.getTables(null, null, tableName, type);
      flag = rs.next();
    } catch (SQLException e) {
      e.printStackTrace();
    }
    return flag;
  }

}
